﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_GetSmallGroup')
BEGIN
    PRINT 'Dropping Procedure proc_cm_GetSmallGroup'
    DROP  Procedure  proc_cm_GetSmallGroup
END
GO

PRINT 'Creating Procedure proc_cm_GetSmallGroup'
GO

CREATE PROCEDURE [dbo].[proc_cm_GetSmallGroup]
	@pSmallGroupId uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON

	SELECT 
		sg.[smallgroup_id]
		,[group_nm]
		,[group_leader_id]
		,[group_leader_nm]
		,[group_leader_email]
		,[group_leader_phone]
		,[description_txt]
		,count(msg.[member_id]) as [member_cnt]
	FROM
		[dbo].[tbl_smallgroup] sg (NOLOCK)
		LEFT JOIN [dbo].[tbl_member_smallgroup] msg (NOLOCK) ON sg.smallgroup_id = msg.smallgroup_id
	WHERE
		sg.[smallgroup_id] = @pSmallGroupId
	GROUP BY
		sg.[smallgroup_id]
		,[group_nm]
		,[group_leader_id]
		,[group_leader_nm]
		,[group_leader_email]
		,[group_leader_phone]
		,[description_txt]
	ORDER BY
		[group_nm] ASC

	SELECT 
		m.[member_id]
		,m.[UserId]
		,u.[UserName] as [username_txt]
		,m.[firstname_txt]
		,m.[lastname_txt]
		,m.[korean_nm]
		,mem.[Email] as [emailaddr_txt]
		,m.[address_txt]
		,m.[address2_txt]
		,m.[city_txt]
		,m.[state_cd]
		,m.[zip_cd]
		,m.[country_nm]
		,m.[homephone_txt]
		,m.[cellphone_txt]
		,m.[birthdate_dt] 
		,m.[icon_filename_txt] 
		,m.[member_since_dt]
		,m.[last_login_dttm]
		,m.[confirmation_guid]
		,m.[created_dttm]
	FROM 
		[dbo].[tbl_member_smallgroup] msg (NOLOCK)
		INNER JOIN [dbo].[tbl_member] m (NOLOCK) ON msg.[member_id] = m.[member_id]
		LEFT JOIN [dbo].[aspnet_Users] u (NOLOCK) ON m.[UserId] = u.[UserId]
		LEFT JOIN [dbo].[aspnet_Membership] mem (NOLOCK) ON m.[UserId] = mem.[UserId]
	WHERE
		msg.[smallgroup_id] = @pSmallGroupId

	SELECT 
		m.[member_id]
		,u.[UserName] + ' (' + ltrim(rtrim(isnull(m.[firstname_txt],'') + ' ' + isnull(m.[lastname_txt],''))) + ')' as [username_txt]
	FROM 
		[dbo].[tbl_member] m (NOLOCK)
		LEFT JOIN [dbo].[aspnet_Users] u (NOLOCK) ON m.[UserId] = u.[UserId]
		LEFT JOIN [dbo].[aspnet_Membership] mem (NOLOCK) ON m.[UserId] = mem.[UserId]
		LEFT JOIN [dbo].[tbl_member_smallgroup] msg (NOLOCK) ON msg.[member_id] = m.[member_id] and msg.[smallgroup_id] = @pSmallGroupId
	WHERE
		msg.[smallgroup_id] IS NULL
	ORDER BY
		u.[UserName]
END
GO

GRANT EXEC ON dbo.proc_cm_GetSmallGroup TO PUBLIC
GO

